package leaveSystem.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import leaveSystem.dao.DAOException;
import leaveSystem.dao.LeaveTypeDAO;
import leaveSystem.model.LeaveType;

public class LeaveTypeDAOImpl implements LeaveTypeDAO {

	    public LeaveType createValueObject() {
	          return new LeaveType();
	    }

	   /* public LeaveType getObject(Connection conn, int leaveTypeId) throws DAOException, SQLException {

	          LeaveType valueObject = createValueObject();
	          valueObject.setLeaveTypeId(leaveTypeId);
	          load(conn, valueObject);
	          return valueObject;
	    }

	    public void load(Connection conn, LeaveType valueObject) throws DAOException, SQLException {

	          String sql = "SELECT * FROM leave_type WHERE (leaveTypeid = ? ) "; 
	          PreparedStatement stmt = null;

	          try {
	               stmt = conn.prepareStatement(sql);
	               stmt.setInt(1, valueObject.getLeaveTypeId()); 

	               singleQuery(conn, stmt, valueObject);

	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }*/

	    public List<LeaveType> loadAll(Connection conn) throws SQLException {

	          String sql = "SELECT * FROM leave_type ORDER BY leaveTypeid ASC ";
	          List<LeaveType> searchResults = listQuery(conn, conn.prepareStatement(sql));

	          return searchResults;
	    }


	    public void insertLeaveType(Connection conn, LeaveType valueObject) throws SQLException {

	          String sql = "";
	          PreparedStatement stmt = null;
	          ResultSet result = null;

	          try {
	               sql = "INSERT INTO leaveapp.leave_type ( leaveTypeName, leaveDescription, createdBy) VALUES (?, ?, ?) ";
	               stmt = conn.prepareStatement(sql);

	               stmt.setString(1, valueObject.getLeaveTypeName()); 
	               stmt.setString(2, valueObject.getLeaveDescription()); 
	               stmt.setString(3, valueObject.getCreatedBy()); 

	               int rowcount = databaseUpdate(conn, stmt);
	               if (rowcount != 1) {
	                    //System.out.println("PrimaryKey Error when updating DB!");
	                    throw new SQLException("PrimaryKey Error when updating DB!");
	               }

	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }

	          sql = "SELECT last_insert_id()";

	          try {
	              stmt = conn.prepareStatement(sql);
	              result = stmt.executeQuery();

	              if (result.next()) {

	                   valueObject.setLeaveTypeId((int)result.getLong(1));

	              } else {
	                   //System.out.println("Unable to find primary-key for created object!");
	                   throw new SQLException("Unable to find primary-key for created object!");
	              }
	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }

	    }


	    public void updateLeaveType(Connection conn, LeaveType valueObject) throws DAOException, SQLException {

	          String sql = "UPDATE leaveapp.leave_type SET leaveTypeName = ?, leaveDescription = ?, createdBy = ? WHERE (leaveTypeid = ? ) ";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              stmt.setString(1, valueObject.getLeaveTypeName()); 
	              stmt.setString(2, valueObject.getLeaveDescription()); 
	              stmt.setString(3, valueObject.getCreatedBy()); 

	              stmt.setInt(4, valueObject.getLeaveTypeId()); 

	              int rowcount = databaseUpdate(conn, stmt);
	              if (rowcount == 0) {
	                   //System.out.println("Object could not be saved! (PrimaryKey not found)");
	                   throw new DAOException("Object could not be saved! (PrimaryKey not found)");
	              }
	              if (rowcount > 1) {
	                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
	                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
	              }
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }


	    public void deleteLeaveType(Connection conn, LeaveType valueObject) throws DAOException, SQLException {

	          String sql = "DELETE FROM leaveapp.leave_type WHERE (leaveTypeid = ? ) ";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              stmt.setInt(1, valueObject.getLeaveTypeId()); 

	              int rowcount = databaseUpdate(conn, stmt);
	              if (rowcount == 0) {
	                   //System.out.println("Object could not be deleted (PrimaryKey not found)");
	                   throw new DAOException("Object could not be deleted! (PrimaryKey not found)");
	              }
	              if (rowcount > 1) {
	                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
	                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
	              }
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }


	    public void deleteAllLeaveType(Connection conn) throws SQLException {

	          String sql = "DELETE FROM leaveapp.leave_type";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              int rowcount = databaseUpdate(conn, stmt);
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

/*
	    public int countAll(Connection conn) throws SQLException {

	          String sql = "SELECT count(*) FROM leave_type";
	          PreparedStatement stmt = null;
	          ResultSet result = null;
	          int allRows = 0;

	          try {
	              stmt = conn.prepareStatement(sql);
	              result = stmt.executeQuery();

	              if (result.next())
	                  allRows = result.getInt(1);
	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }
	          return allRows;
	    }*/

//searching method
	    public List<LeaveType> findLeaveType(Connection conn, LeaveType valueObject) throws SQLException {

	          List<LeaveType> searchResults;

	          boolean first = true;
	          StringBuffer sql = new StringBuffer("SELECT * FROM leaveapp.leave_type WHERE 1=1 ");

	          if (valueObject.getLeaveTypeId() != 0) {
	              if (first) { first = false; }
	              sql.append("AND leaveTypeid = ").append(valueObject.getLeaveTypeId()).append(" ");
	          }

	          if (valueObject.getLeaveTypeName() != null) {
	              if (first) { first = false; }
	              sql.append("AND leaveTypeName LIKE '").append(valueObject.getLeaveTypeName()).append("%' ");
	          }

	          if (valueObject.getLeaveDescription() != null) {
	              if (first) { first = false; }
	              sql.append("AND leaveDescription LIKE '").append(valueObject.getLeaveDescription()).append("%' ");
	          }

	          if (valueObject.getCreatedBy() != null) {
	              if (first) { first = false; }
	              sql.append("AND createdBy LIKE '").append(valueObject.getCreatedBy()).append("%' ");
	          }

	          sql.append("ORDER BY leaveTypeid ASC ");

	          // Use loadAll if all rows must be returned.
	          if (first)
	               searchResults = new ArrayList<LeaveType>();
	          else
	               searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));

	          return searchResults;
	    }

	    protected int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException {
	          int result = stmt.executeUpdate();
	          return result;
	    }

/*	    protected void singleQuery(Connection conn, PreparedStatement stmt, LeaveType valueObject) 
	          throws DAOException, SQLException {

	          ResultSet result = null;

	          try {
	              result = stmt.executeQuery();

	              if (result.next()) {

	                   valueObject.setLeaveTypeId(result.getInt("leaveTypeid")); 
	                   valueObject.setLeaveTypeName(result.getString("leaveTypeName")); 
	                   valueObject.setLeaveDescription(result.getString("leaveDescription")); 
	                   valueObject.setCreatedBy(result.getString("createdBy")); 

	              } else {
	                    //System.out.println("LeaveType Object Not Found!");
	                    throw new DAOException("LeaveType Object Not Found!");
	              }
	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }
	    }*/

	    protected List<LeaveType> listQuery(Connection conn, PreparedStatement stmt) throws SQLException {

	          ArrayList<LeaveType> searchResults = new ArrayList<LeaveType>();
	          ResultSet result = null;

	          try {
	              result = stmt.executeQuery();

	              while (result.next()) {
	                   LeaveType temp = createValueObject();

	                   temp.setLeaveTypeId(result.getInt("leaveTypeid")); 
	                   temp.setLeaveTypeName(result.getString("leaveTypeName")); 
	                   temp.setLeaveDescription(result.getString("leaveDescription")); 
	                   temp.setCreatedBy(result.getString("createdBy")); 

	                   searchResults.add(temp);
	              }

	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }

	          return (List<LeaveType>)searchResults;
	    }
}
